package cn.ffcs.bpmpvc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.LockMode;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import cn.ffcs.base.web.PageControl;
import cn.ffcs.bpm.model.NextTep;
import cn.ffcs.bpm.model.ProcTranRecord;
import cn.ffcs.bpm.model.ProcessConfig;
import cn.ffcs.bpm.model.ProcessConfigDetail;
import cn.ffcs.bpm.model.TransactProcessStatus;
import cn.ffcs.bpmpvc.dao.FlowPvcDao;
import cn.ffcs.bpmpvc.graph.Node;
import cn.ffcs.bpmpvc.model.BpmSwimlane;
import cn.ffcs.bpmpvc.svc.ProcessPvcService;
import cn.ffcs.bpmpvc.svc.TaskService;
import cn.ffcs.bpmpvc.web.views.TaskView;
import cn.ffcs.organ.model.Person;
import cn.ffcs.security.model.User;
import cn.ffcs.system.dict.model.Dict;

public class FlowPvcDaoImpl extends HibernateDaoSupport implements FlowPvcDao
{
    private JdbcTemplate jdbcTemplate;
    public void save(Object obj)
    {
        // TODO Auto-generated method stub
        getHibernateTemplate().saveOrUpdate(obj);
    }
    
    
    @SuppressWarnings("unchecked")
	public ProcessConfig getProcessConfig(String processId)
    {
        // TODO Auto-generated method stub
        String hql="select t from ProcessConfig t where t.id=?";
        List list=getHibernateTemplate().find(hql,Integer.parseInt(processId));
        if(list!=null&&list.size()>0){
            return (ProcessConfig)list.get(0);
        }
        return null;
    }
    @SuppressWarnings("unchecked")
	public ProcessConfigDetail getProcessConfigDetail(String name,String processId){
    	 String hql="select t from ProcessConfigDetail t where t.processConfig.id=? and t.name=?";
         List list=getHibernateTemplate().find(hql, new Object[]{Integer.parseInt(processId),name});
         if(list!=null&&list.size()>0){
             return (ProcessConfigDetail)list.get(0);
         }
         return null;
    }
    @SuppressWarnings("unchecked")
	public ProcessConfigDetail getProcessStartNode(String processId)
    {
        // TODO Auto-generated method stub
        String hql="select t from ProcessConfigDetail t where t.processConfig.id=? and t.nodeType=?";
        List list=getHibernateTemplate().find(hql, new Object[]{Integer.parseInt(processId),"0"});
        if(list!=null&&list.size()>0){
            return (ProcessConfigDetail)list.get(0);
        }
        return null;
    }
    @SuppressWarnings("unchecked")
	public User getUserByPerson(Integer personId){
    	String hql="select t from User t where t.personID=?";
    	List list=getHibernateTemplate().find(hql, new Object[]{personId.toString()});
        if(list!=null&&list.size()>0){
            return (User)list.get(0);
        }
        return null;
    }
    
    @SuppressWarnings("unchecked")
	public List<ProcessConfigDetail> findNextTaskNode(Integer currentTaskNodeId)
    {
        // TODO Auto-generated method stub
        String hql="select t.nextConfig from NextTep t where t.processDetail.id=? order by t.nextConfig.orderBy asc";
        List<ProcessConfigDetail> list=getHibernateTemplate().find(hql, new Object[]{currentTaskNodeId});
        return list;
    }

    @SuppressWarnings("unchecked")
	public List<ProcessConfigDetail> findPretTaskNode(Integer currentTaskNodeId)
    {
        // TODO Auto-generated method stub
        String hql="select t.processDetail from NextTep t where t.nextConfig.id=?";
        List list=getHibernateTemplate().find(hql, new Object[]{currentTaskNodeId});
        return list;
    }
    @SuppressWarnings("unchecked")
	public List<ProcessConfigDetail> findAllProcessDetail(Integer currentTaskNodeId)
    {
        // TODO Auto-generated method stub
        String hql="select t from ProcessConfigDetail t where t.processConfig.id=? and t.nodeType!=3 and t.nodeType!=4 order by t.orderBy asc";
        List list=getHibernateTemplate().find(hql, new Object[]{currentTaskNodeId});
        return list;
    }
   
    public ProcTranRecord getTask(Integer taskId){
        return getTask(taskId,false);
    }

    @SuppressWarnings("unchecked")
	public ProcTranRecord getTask(Integer taskId,boolean isLock){
        String hql="select t from ProcTranRecord t where t.id=?";
        Session session=getSession();
        Query query=session.createQuery(hql);
        query.setParameter(0, taskId);
        if(isLock){
            query.setLockMode("t", LockMode.UPGRADE);
        }
        List list=query.list();
        if(list!=null&&list.size()>0){
            return (ProcTranRecord)list.get(0);
        }
        return null;
    }
    
    @SuppressWarnings("unchecked")
	public List<ProcTranRecord> findTaskList(Integer processInstId,Integer idArr[]){
        String idStr=StringUtils.join(idArr, ",");
        String hql="select distinct t from ProcTranRecord t where t.procinstId=?" +
        		" and (t.orderBy is null or t.orderBy=1) and (t.taskType='01' or t.taskType is null) and t.processStep.id in("+idStr+") order by t.id desc";
        List list=getHibernateTemplate().find(hql, processInstId);
        return list;
    }
    
    @SuppressWarnings("unchecked")
	public ProcTranRecord getLiveProcTranRecord(Integer procinstId){
    	ProcTranRecord procTranRecord=new ProcTranRecord();
    	String sql=" from ProcTranRecord t where t.state='active' and t.procinstId="+procinstId;
    	List list=getHibernateTemplate().find(sql);
    	if(list==null||list.size()==0){
    		return null;
    	}else{
	    	procTranRecord=(ProcTranRecord) list.get(0);
	    	return procTranRecord;
    	}
    }
    @SuppressWarnings("unchecked")
	public List<ProcTranRecord> findTaskList(Integer processInstId){
        String sql="select t1.id,t2.name,t1.transact_info,t3.name,t1.receive_time,t1.transact_time,t1.procinst_id,t1.matters,t2.flow_explain,t1.task_type";
        sql+=" from t_bpm_proc_tran_record t1, t_bpm_processconfigdetail t2,t_organ_person t3";
        sql+=" where t1.process_step=t2.id and t1.transactor=t3.id";
        sql+=" and t1.procinst_id=?";
        sql+=" order by to_number(t1.id) asc";
        List list=getJdbcTemplate().query(sql, new Object[]{processInstId}, new RowMapper(){

            public Object mapRow(ResultSet rs, int arg1) throws SQLException
            {
                // TODO Auto-generated method stub
                ProcTranRecord procTranRecord=new ProcTranRecord(); 
                ProcessConfigDetail processStep=new ProcessConfigDetail();
                Person transactor=new Person();
                procTranRecord.setId(Integer.valueOf(rs.getString(1)));
                processStep.setName(rs.getString(2));
                procTranRecord.setProcessStep(processStep);
                String tmp=rs.getString(3);
                if(StringUtils.isNotBlank(tmp)){
                	tmp=StringUtils.replace(tmp, "\n", "<br/>");
                }
                procTranRecord.setTransactInfo(tmp);
                transactor.setName(rs.getString(4));
                procTranRecord.setTransactor(transactor);
                procTranRecord.setReceiveTime(rs.getTimestamp(5));
                procTranRecord.setTransactTime(rs.getTimestamp(6));
                procTranRecord.setProcinstId(Integer.valueOf(rs.getString(7)));
                procTranRecord.setMatters(rs.getString(8));
                procTranRecord.setTaskType(rs.getString(10));
                return procTranRecord;
            }});
        return list;
    }   

    public void updateCompletForkTask(Integer parentExecuteId,String rollBackInfo){
        String hql="update ProcTranRecord t set t.state=?,t.transactTime=?,t.orderBy=?,t.matters=?" +
        		" where t.id in(select t2.actId from TransactProcessStatus t2 where t2.parentId=?)";
        Session session=getSession();
        Query query=session.createQuery(hql);
        query.setParameter(0, TaskService.TASK_STATE_END);
        query.setParameter(1, new Date());
        query.setParameter(2, 2);
        query.setParameter(3, rollBackInfo);
        query.setParameter(4, parentExecuteId);
        query.executeUpdate();
    }

    public void updateCompleteTask(Integer taskId,String content){
        String hql="update ProcTranRecord t set t.state=?,t.transactTime=?,t.orderBy=?,t.matters=?" +
                " where t.id=?";
        Session session=getSession();
        Query query=session.createQuery(hql);
        query.setParameter(0, TaskService.TASK_STATE_END);
        query.setParameter(1, new Date());
        query.setParameter(2, 2);
        query.setParameter(3, content);
        query.setParameter(4, taskId);
        query.executeUpdate();
    }
    
    @SuppressWarnings("unchecked")
	public List findTaskList(TaskView taskView, PageControl pageControl)
    {
        // TODO Auto-generated method stub
        return null;
    }
    
    @SuppressWarnings("unchecked")
	public List findProcessSubExecuteInstList(Integer parentExecuteId,String state,boolean isLock)
    {
        // TODO Auto-generated method stub
        String hql="select t from TransactProcessStatus t where t.parentId=? and t.state=?";
        Session session=getSession();
        Query query=session.createQuery(hql);
        query.setParameter(0, parentExecuteId);
        query.setParameter(1, state);
        if(isLock){
            query.setLockMode("t", LockMode.UPGRADE);
        }
        List list=query.list();
        //List list=getHibernateTemplate().find(hql, new Object[]{parentExecuteId,state});
        return list;
    }

    @SuppressWarnings("unchecked")
	public TransactProcessStatus getProcessExecuteInst(Integer taskId)
    {
        // TODO Auto-generated method stub
        
        String hql="select t from TransactProcessStatus t where t.actId=?";
        List list=getHibernateTemplate().find(hql, taskId);
        if(list!=null&&list.size()>0){
            return (TransactProcessStatus)list.get(0);
        }
        return null;
    }

    @SuppressWarnings("unchecked")
	public TransactProcessStatus getProcessExecuteInstById(Integer executeInstId)
    {
        // TODO Auto-generated method stub
        
        String hql="select t from TransactProcessStatus t where t.id=?";
        List list=getHibernateTemplate().find(hql, executeInstId);
        if(list!=null&&list.size()>0){
            return (TransactProcessStatus)list.get(0);
        }
        return null;
    }
    /**
     *  通过流程实例id取对象
     */
    @SuppressWarnings("unchecked")
	public TransactProcessStatus getProcessExecuteInstByProcinstId(Integer procinstId)
    {
        // TODO Auto-generated method stub
        
        String hql="select t from TransactProcessStatus t where t.procinstId=?";
        List list=getHibernateTemplate().find(hql, procinstId);
        if(list!=null&&list.size()>0){
            return (TransactProcessStatus)list.get(0);
        }
        return null;
    }
    public void deleteProcessSubExecute(Integer parentExecuteId){
        String hql="delete from TransactProcessStatus t where t.parentId=?";
        Session session=getSession();
        Query query=session.createQuery(hql);
        query.setParameter(0, parentExecuteId);
        query.executeUpdate();
    }
    
    public void deleteProcessSubExecute(Integer parentExecuteId,String state){
        String hql="delete from TransactProcessStatus t where t.parentId=? and t.state=?";
        Session session=getSession();
        Query query=session.createQuery(hql);
        query.setParameter(0, parentExecuteId);
        query.setParameter(1, state);
        query.executeUpdate();
    }

    @SuppressWarnings("unchecked")
	public List<Dict> findDocumentTypeByNodeId(Integer nodeId)
    {
        // TODO Auto-generated method stub
        String hql="select t.docType from DocumentType t where t.processDetail.id=?";
        List list=getHibernateTemplate().find(hql, nodeId);
        return list;
    }
    
    @SuppressWarnings("unchecked")
	public List findRunExecuteList(Integer procInstId,Integer nodeIdArr[]){
        String nodeIdsStr=StringUtils.join(nodeIdArr,",");
        String hql="select t from TransactProcessStatus t where t.procinstId=? and t.state!=? and t.currProcessConfigDetail.id in("+nodeIdsStr+")";
        List list=getHibernateTemplate().find(hql, new Object[]{procInstId,ProcessPvcService.INACTIVE_JOIN});
        return list;
    }

    @SuppressWarnings("unchecked")
	public List findAllExecuteList(Integer procInstId,Integer nodeIdArr[]){
        String nodeIdsStr=StringUtils.join(nodeIdArr,",");
        String hql="select t from TransactProcessStatus t where t.procinstId=? and t.currProcessConfigDetail.id in("+nodeIdsStr+")";
        List list=getHibernateTemplate().find(hql, new Object[]{procInstId});
        return list;
    }
    
    public void deleteTaskAssignUser(Integer executeId){
        String hql="delete from Transactor t where t.currProcess.id=?";
        Session session=getSession();
        Query query=session.createQuery(hql);
        query.setParameter(0, executeId);
        query.executeUpdate();
    }
    
    @SuppressWarnings("unchecked")
	public List findTaskAssignUser(Integer executeId){
        String hql="select t from Transactor t inner join t.processConfigDetail t2 inner join t.userId t3 where t.currProcess.id=?";
        List list=getHibernateTemplate().find(hql, executeId);
        return list;
    }

    @SuppressWarnings("unchecked")
	public BpmSwimlane getBpmSwimlane(String swinlaneName,Integer procinstId){
        String hql="select t from BpmSwimlane t where t.swinlane=? and t.procinstId=?";
        List list=getHibernateTemplate().find(hql, new Object[]{swinlaneName,procinstId});
        if(list!=null&&list.size()>0){
            return (BpmSwimlane)list.get(0);
        }
        return null;
    }
    /**
     * 取得swimlane信息
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
	public List<BpmSwimlane> findBpmSwimlaneList(Integer procinstId){
        
        String sql="select t1.db_id,t1.swimlane_,t1.assign_,t2.name,t1.procinst_id from t_bpm_swimlane t1,t_organ_person t2 where t1.assign_=t2.id and t1.procinst_id=?";
        System.out.println("findBpmSwimlaneList:"+sql+":"+procinstId);
        List list=getJdbcTemplate().query(sql, new Object[]{procinstId}, new RowMapper(){

            public Object mapRow(ResultSet rs, int arg1) throws SQLException
            {
                // TODO Auto-generated method stub
                BpmSwimlane bpmSwimlane=new BpmSwimlane();
                bpmSwimlane.setId(Integer.valueOf(rs.getString(1)));
                bpmSwimlane.setSwinlane(rs.getString(2));
                bpmSwimlane.setAssign(rs.getString(3));
                bpmSwimlane.setAssingName(rs.getString(4));
                bpmSwimlane.setProcinstId(Integer.valueOf(rs.getString(5)));
                return bpmSwimlane;
            }});
        return list;
    }

    @SuppressWarnings("unchecked")
	public List<Person> findPersonByRoleName(String roleName)
    {
        // TODO Auto-generated method stub
        String sql="select distinct t3.id,t3.name" +
                " from t_secu_role_rela_user t1,t_secu_role t2,t_secu_user t6,t_organ_person t3" +
                " where t1.secu_role=t2.id and t1.secu_user=t6.id and t6.person=t3.id and t1.end>sysdate and t2.name=?";
        System.out.println("findPersonByRoleName:"+sql);
        List list=getJdbcTemplate().query(sql, new Object[]{roleName}, new RowMapper(){

            public Object mapRow(ResultSet rs, int arg1) throws SQLException
            {
                // TODO Auto-generated method stub
                Person person=new Person();
                person.setId(Integer.valueOf(rs.getString(1)));
                person.setName(rs.getString(2));
                return person;
            }});
        return list;
    }
    
    public List<Person> findPersonByRoleName(String roleName,boolean isOrg)
    {
        // TODO Auto-generated method stub
        String sql="select distinct t3.id,t3.name,t5.name orgName" +
                " from t_secu_role_rela_user t1,t_secu_role t2,t_secu_user t6," +
                "t_organ_person t3,t_organ_org_rela_person t4,t_organ_organization t5" +
                " where t1.secu_role=t2.id and t1.secu_user = t6.id and t6.person=t3.id" +
                " and t4.person = t3.id and t4.organization = t5.id and t1.end>sysdate and t2.name=? order by t5.name asc,t3.name asc";
        System.out.println("findPersonByRoleName:"+sql);
        List list=getJdbcTemplate().query(sql, new Object[]{roleName}, new RowMapper(){

            public Object mapRow(ResultSet rs, int arg1) throws SQLException
            {
                // TODO Auto-generated method stub
                Person person=new Person();
                person.setId(Integer.valueOf(rs.getString(1)));
                person.setName(rs.getString(2)+"【"+rs.getString(3)+"】");
                person.setOrgName(rs.getString(3));
                return person;
            }});
        return list;
    }
    
    public boolean checkPersonHasRole(String roleName,String personId)
    {
        // TODO Auto-generated method stub
        String sql="select count(*)" +
                " from t_secu_role_rela_user t1,t_secu_role t2,t_secu_user t3" +
                " where t1.secu_role=t2.id and t1.secu_user = t3.id" +
                " and t1.end>sysdate and t2.name=? and t3.person=?";
        System.out.println("checkPersonHasRole:"+sql);
        int count=getJdbcTemplate().queryForInt(sql, new Object[]{roleName,personId});
        if(count>0){
        	return true;
        }
        return false;
    }
    
    @SuppressWarnings("unchecked")
	public List<Node> findNodeList(String processId){
    	String sql="select t.id,t.name,t.node_x,t.node_y,t.node_w,t.node_h,node_type from t_bpm_processconfigdetail t where t.process_config=? and t.end>sysdate";
    	List<Node> list=getJdbcTemplate().query(sql, new Object[]{processId}, new RowMapper(){
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				Node node=new Node();
				node.setId(rs.getString(1));
				node.setName(rs.getString(2));
				String tmp=rs.getString(3);
				if(StringUtils.isNotBlank(tmp)){
					node.setX(Integer.parseInt(tmp));
				}else{
					node.setX(300);
				}
				
				tmp=rs.getString(4);
				if(StringUtils.isNotBlank(tmp)){
					node.setY(Integer.parseInt(tmp));
				}
				
				tmp=rs.getString(5);
				if(StringUtils.isNotBlank(tmp)){
					node.setW(Integer.parseInt(tmp));
				}else{
					node.setW(150);
				}
				
				tmp=rs.getString(6);
				if(StringUtils.isNotBlank(tmp)){
					node.setH(Integer.parseInt(tmp));
				}else{
					node.setH(40);
				}
				
				tmp=rs.getString(7);
				if("0".equals(tmp)){
					node.setType("start");
				}else if("1".equals(tmp)){
					node.setType("end");
				}else if("3".equals(tmp)){
					node.setType("fork");
				}else if("4".equals(tmp)){
					node.setType("join");
				}
				return node;
			}});
    	return list;
    }

    @SuppressWarnings("unchecked")
	public List<Node> findNexNodeList(String processId){
    	String sql="select t1.next_config, t2.name,t2.node_x,t2.node_y,t2.node_w,t2.node_h,t1.process_detail from t_bpm_nexttep t1,t_bpm_processconfigdetail t2 where t1.next_config = t2.id and t2.process_config=?";
    	List<Node> list=getJdbcTemplate().query(sql, new Object[]{processId}, new RowMapper(){
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				Node node=new Node();
				node.setId(rs.getString(1));
				node.setName(rs.getString(2));
				String tmp=rs.getString(3);
				if(StringUtils.isNotBlank(tmp)){
					node.setX(Integer.parseInt(tmp));
				}else{
					node.setX(300);
				}
				
				tmp=rs.getString(4);
				if(StringUtils.isNotBlank(tmp)){
					node.setY(Integer.parseInt(tmp));
				}
				
				tmp=rs.getString(5);
				if(StringUtils.isNotBlank(tmp)){
					node.setW(Integer.parseInt(tmp));
				}else{
					node.setW(150);
				}
				
				tmp=rs.getString(6);
				if(StringUtils.isNotBlank(tmp)){
					node.setH(Integer.parseInt(tmp));
				}else{
					node.setH(40);
				}
				node.setParentId(rs.getString(7));
				return node;
			}});
    	return list;
    }
    
    @SuppressWarnings("unchecked")
	public List<Node> findRunNodeList(String procInstId){
    	//排除阅办节点
    	String sql="select t.process_step,t2.name,t2.flow_explain from t_bpm_proc_tran_record t,t_bpm_processconfigdetail t2" +
    			" where t.process_step=t2.id and t.procinst_id=? and t.state=? and (t.TASK_TYPE=? or t.TASK_TYPE=?)";
    	List<Node> list=getJdbcTemplate().query(sql, new Object[]{procInstId,"active","01","02"}, new RowMapper(){
			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				Node node=new Node();
				node.setId(rs.getString(1));
				node.setName(rs.getString(2));
				return node;
			}});
    	return list;
    }
    
    public String getFlowProcessId(String procInstId){
    	String sql="select distinct t.curr_process_config from t_bpm_transactprocessstatus t where t.id=?";
    	Object obj=getJdbcTemplate().queryForObject(sql,new Object[]{procInstId}, String.class);
    	return (String)obj;
    }
    
    public List findAllProcessList(){
    	String hql="select t from ProcessConfig t where t.end>=sysdate";
    	return getHibernateTemplate().find(hql);
    }
    
    public List findAllPriorNextTep(String startNodeId)
    {
        String hql = "select b.* from t_bpm_nexttep b start with b.process_detail='"+startNodeId+"' connect by b.process_detail=prior b.next_config";
        return getSession().createSQLQuery(hql).addEntity("b",NextTep.class).list();
    }
    
    public List findRunTaskList(Integer procInstId){
    	//String hql="select t from ProcTranRecord t inner join t.processStep t2 inner join t.transactor where t.state=? and t.taskType=? and t.procinstId=?";
    	String sql="select t1.id,t1.process_step,t2.id,t2.name,t1.state,t3.name from t_bpm_proc_tran_record t1,t_organ_person t2,t_bpm_processconfigdetail t3 where t1.transactor=t2.id and t1.process_step=t3.id" +
    			" and t1.state=? and t1.task_type=? and t1.procinst_id=? order by to_number(t1.id) desc";
    	
    	List list=getJdbcTemplate().query(sql, new Object[]{"active","01",procInstId}, new RowMapper(){

			public Object mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				ProcTranRecord procTranRecord=new ProcTranRecord();
				procTranRecord.setId(rs.getInt(1));
				ProcessConfigDetail configDetail=new ProcessConfigDetail();
				configDetail.setId(rs.getInt(2));
				configDetail.setName(rs.getString(6));
				procTranRecord.setProcessStep(configDetail);
				Person transactor=new Person();
				transactor.setName(rs.getString(4));
				procTranRecord.setTransactor(transactor);
				procTranRecord.setState(rs.getString(5));
				return procTranRecord;
			}
    	});
    	return list;
    }
    
    
	public int getMaxOrderBy(Integer procInstId) {
		String sql="select max(t.order_by) as order_by from t_bpm_processconfigdetail t where t.id in ("
				+"select t.curr_process_config_detail from t_bpm_transactprocessstatus t where t.procinst_id='"+procInstId+"'"
				+")";
		int i=getJdbcTemplate().queryForInt(sql);
		
		return i;
	}
	
	public void endProcessInst(String procinstId){
		String sql="update t_bpm_proc_tran_record t set t.transact_time=sysdate,t.state=?,t.matters=? where t.procinst_id=? and t.state=?";
		getJdbcTemplate().update(sql, new Object[]{"end","强制结束",procinstId,"active"});
		sql="update t_bpm_transactprocessstatus t set t.end_time=sysdate,t.state=? where t.procinst_id=? or t.parent_id=?";
		getJdbcTemplate().update(sql, new Object[]{"inactive-root",procinstId,procinstId});
	}
	
    public JdbcTemplate getJdbcTemplate()
    {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)
    {
        this.jdbcTemplate = jdbcTemplate;
    }

    
}
